package com.javaEE.tes.dao;

import com.javaEE.tes.dao.dto.StudentClass1;
import com.javaEE.tes.entities.Class1;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface Class1Repository extends CrudRepository<Class1,Long> {

    @Autowired
    List<Class1> findAll();
    @Query(nativeQuery = true,value = "SELECT c.class1Name,c.college,COUNT(s.student_id) as num,t.name1 " +
            "FROM class1 AS c JOIN teacher AS t ON c.teacher_id=t.teacher_id LEFT JOIN student AS s ON c.class1Id=s.class_id "+
            "GROUP BY c.class1Name, c.college, t.name1 ORDER BY c.class1Name;"
    )
    List<StudentClass1> class1Student();
}
